package cn.xm.lingshi.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.xm.lingshi.dao.ShopDao;
import cn.xm.lingshi.pojo.Brand;
import cn.xm.lingshi.pojo.Kind;
import cn.xm.lingshi.pojo.Shop;
import cn.xm.lingshi.pojo.Type;
import cn.xm.lingshi.utlis.BaseDao;

public class ShopDaoImpl extends BaseDao implements ShopDao {

	@Override
	public List<Shop> getShopBySalesTop5() throws Exception {
		String sql = "SELECT id,`describe`,shoporice,shoptype,sales,shopimg,kindid,brandid,shopname,kucun FROM shop"
				+ " ORDER BY sales DESC LIMIT 4";
		ResultSet rs = super.executeQuery(sql, null);
		Shop shop = null;
		List<Shop> list = new ArrayList<Shop>();
		while (rs != null && rs.next()) {
			shop = new Shop();
			shop.setBrandid(rs.getInt("brandid"));
			shop.setDescribe(rs.getString("describe"));
			shop.setId(rs.getInt("id"));
			shop.setKindid(rs.getInt("kindid"));
			shop.setKucun(rs.getInt("kucun"));
			shop.setSales(rs.getInt("sales"));
			shop.setShopimg(rs.getString("shopimg"));
			shop.setShopname(rs.getString("shopname"));
			shop.setShoporice(rs.getDouble("shoporice"));
			shop.setShoptype(rs.getInt("shoptype"));
			list.add(shop);
		}
		super.closeResource(rs);
		return list;
	}

	@Override
	public List<Type> getType() throws Exception {
		List<Type> list = new ArrayList<Type>();
		String sql = "SELECT typeid,`type` FROM `type`";
		ResultSet rs = super.executeQuery(sql, null);
		Type type = null;
		while (rs != null && rs.next()) {
			type = new Type();
			type.setTypeid(rs.getInt("typeid"));
			type.setType(rs.getString("type"));
			list.add(type);
		}
		return list;
	}

	@Override
	public List<Kind> getKindByTypeId(int typeId) throws Exception {
		List<Kind> list = new ArrayList<Kind>();
		String sql = "SELECT typeid,kindid,kind FROM kind WHERE typeid=?";
		Object[] params = { typeId };
		ResultSet rs = super.executeQuery(sql, params);
		Kind kind = null;
		while (rs != null && rs.next()) {
			kind = new Kind();
			kind.setTypeid(rs.getInt("typeid"));
			kind.setKind(rs.getString("kind"));
			kind.setKindid(rs.getInt("kindid"));
			list.add(kind);
		}
		return list;
	}

	@Override
	public int addShop(Shop shop) throws Exception {
		String sql = "INSERT INTO shop (`describe`,shoporice,shoptype,shopimg,kindid,brandid,shopname,kucun)"
				+ " VALUES (?,?,1,?,?,?,?,?)";
		Object[] params = { shop.getDescribe(), shop.getShoporice(),
				shop.getShopimg(), shop.getKindid(), shop.getBrandid(),
				 shop.getShopname(), shop.getKucun() };
		
		return super.executeUpdate(sql, params);
	}

	@Override
	public List<Brand> getBrand() throws Exception {
		List<Brand> list = new ArrayList<Brand>();
		String sql = "SELECT brandid,`brandname` FROM `pin`";
		ResultSet rs = super.executeQuery(sql, null);
		Brand brand = null;
		while (rs != null && rs.next()) {
			brand = new Brand();
			brand.setBrandid(rs.getInt("brandid"));
			brand.setBrand(rs.getString("brandname"));
			list.add(brand);
		}
		return list;
	}
	@Override
	public int addShopFlavour(int flavid,int shopid) throws Exception {
		String sql="INSERT INTO shopflav(shopid,flavid) VALUES (?,?);";
		Object[] params={shopid,flavid};
		return super.executeUpdate(sql, params);
	}

	@Override
	public int getShop(String Shopname) throws Exception {
		String sql = "SELECT id FROM shop WHERE shopname=?";
		Object[] params={Shopname};
		ResultSet rs = super.executeQuery(sql, params);
		if (rs != null && rs.next()) {
			return rs.getInt("id");
		}
		return 0;
	}

	@Override
	public List<Shop> getShopByTime() throws Exception {
		String sql = "SELECT id,`describe`,shoporice,shoptype,sales,shopimg,kindid,brandid,shopname,kucun FROM shop"
				+ " ORDER BY `time` DESC LIMIT 6";
		ResultSet rs = super.executeQuery(sql, null);
		Shop shop = null;
		List<Shop> list = new ArrayList<Shop>();
		while (rs != null && rs.next()) {
			shop = new Shop();
			shop.setBrandid(rs.getInt("brandid"));
			shop.setDescribe(rs.getString("describe"));
			shop.setId(rs.getInt("id"));
			shop.setKindid(rs.getInt("kindid"));
			shop.setKucun(rs.getInt("kucun"));
			shop.setSales(rs.getInt("sales"));
			shop.setShopimg(rs.getString("shopimg"));
			shop.setShopname(rs.getString("shopname"));
			shop.setShoporice(rs.getDouble("shoporice"));
			shop.setShoptype(rs.getInt("shoptype"));
			list.add(shop);
		}
		super.closeResource(rs);
		return list;
	}
	@Override
	public int queryTotalRecord(String searchTitle, int searchTId)
			throws Exception {
		List<Object> paramsList=new ArrayList<Object>();
		int row=0;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT COUNT(1) FROM shop");
		sql.append(" where 1=1 ");
		if(searchTitle!=null && !"".equals(searchTitle)){
			sql.append(" and `describe` like ?");
			paramsList.add("%"+searchTitle+"%");
		}
		if(searchTId>0){
			sql.append(" and kindid=?");
			paramsList.add(searchTId);
		}
		Object[] params=paramsList.toArray();
		ResultSet rs=super.executeQuery(sql.toString(), params);
		if(rs!=null && rs.next()){
			row=rs.getInt(1);
		}
		super.closeResource(rs);
		return row;
	}

	@Override
	public List<Shop> queryNewsByOffset(int offSet, int pageSize,
			String searchTitle, int searchTId) throws Exception {
		List<Shop> list = new ArrayList<Shop>();
		List<Object> paramsList=new ArrayList<Object>();
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT id,`describe`,shoporice,shoptype,sales,shopimg,kindid,brandid,shopname,kucun");
		sql.append(" FROM shop where 1=1 ");
		if(searchTitle!=null && !"".equals(searchTitle)){
			sql.append(" and `describe` like ?");
			paramsList.add("%"+searchTitle+"%");
		}
		if(searchTId>0){
			sql.append(" and kindid=?");
			paramsList.add(searchTId);
		}
		sql.append(" ORDER BY id DESC LIMIT ?,? ");
		System.out.println("发送的sql:"+sql);
		paramsList.add(offSet);
		paramsList.add(pageSize);
		Object[] params=paramsList.toArray();
		ResultSet rs = super.executeQuery(sql.toString(), params);
		while (rs != null && rs.next()) {
			Shop news = new Shop();
			news.setId(rs.getInt(1));
			news.setDescribe(rs.getString(2));
			news.setShoporice(rs.getDouble(3));
			news.setShoptype(rs.getInt(4));
			news.setSales(rs.getInt(5));
			news.setShopimg(rs.getString(6));
			news.setKindid(rs.getInt(7));
			news.setBrandid(rs.getInt(8));
			news.setShopname(rs.getString(9));
			news.setKucun(rs.getInt(10));
			list.add(news);
		}
		super.closeResource(rs);
		return list;
	}
}
